package com.situ.company.department.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.situ.company.department.model.DepartmentModel;
import com.situ.company.util.JDBCUtil;

//数据层--通过Java代码来实现数据库中数据的增删改查（执行sql）
//jdbc
public class DepartmentDao {

    public Integer insert(DepartmentModel model) {
        String sql = "insert into department(code,name,tel) values(?,?,?)";
        return JDBCUtil.update(sql, model.getCode(), model.getName(), model.getTel());
    }

    public Integer delete(DepartmentModel model) {
        String sql = "delete from department where code = ?";
        return JDBCUtil.update(sql, model.getCode());
    }

    public Integer update(DepartmentModel model) {
        String sql = "update department set name=?,tel=? where code=?";
        return JDBCUtil.update(sql, model.getName(), model.getTel(), model.getCode());
    }

    public List<DepartmentModel> select(DepartmentModel model) {
        StringBuffer sql = new StringBuffer(
                "select code,name,tel,(select count(1) from employee where code_dept=department.code)count from department where 1=1 ");
        List<Object> vals = appendWhere(sql, model);
        Map<String, String> fields = new HashMap<>();
        fields.put("code", "code");
        fields.put("name", "name");
        fields.put("tel", "tel");
        fields.put("count", "count");
        return JDBCUtil.query(sql.toString(), vals, DepartmentModel.class, fields);
        // List<DepartmentModel> result = new ArrayList<>();
        // Connection con = null;
        // PreparedStatement ps = null;
        // ResultSet rs = null;
        // try {
        // con = JDBCUtil.getConnection();
        // ps = con.prepareStatement(sql.toString());
        // for (int i = 0; i < list.size(); i++)
        // ps.setObject(i + 1, list.get(i));
        // rs = ps.executeQuery();
        // while (rs.next()) {
        // String code = rs.getString("code");
        // String name = rs.getString("name");
        // String tel = rs.getString("tel");
        // result.add(new DepartmentModel(code, name, tel));
        // }
        // } catch (SQLException e) {
        // e.printStackTrace();
        // } finally {
        // JDBCUtil.close(con, ps, rs);
        // }
        // return result;
    }

    private List<Object> appendWhere(StringBuffer sql, DepartmentModel model) {
        List<Object> list = new ArrayList<>();
        String code = model.getCode();
        if (code != null && !code.trim().isEmpty()) {
            sql.append(" and code like ? ");// and code like '计算机1'
            list.add(code);
        }
        String name = model.getName();
        if (name != null && !name.trim().isEmpty()) {
            sql.append(" and name like ? ");// and name like '%计算机%'
            list.add(name);
        }
        String tel = model.getTel();
        if (tel != null) {
            sql.append(" and tel like ? ");
            list.add(tel);
        }
        if (model.isPageOn()) {
            sql.append(" limit ?,? ");
            list.add(model.getRowStart());
            list.add(model.getPageLimit());
        }
        return list;
    }

    public Integer selectCount(DepartmentModel model) {
        StringBuffer sql = new StringBuffer("select count(1) from department where 1=1 ");
        List<Object> vals = appendWhere(sql, model);
        return JDBCUtil.queryInt(sql.toString(), vals);
    }

}
